#    Copyright 2010, 2011, 2012 Tri-County Electric Cooperative, St. Matthews, South Carolina
#    This file is part of COOPIP.
#    COOPIP is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#    COOPIP is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#    You should have received a copy of the GNU General Public License
#    along with COOPIP.  If not, see <http://www.gnu.org/licenses/>.

#************************BEGIN Add Include Files to Path************************
import sys
NeededPaths=['/opt/coopip/djangoproject/include', '/opt/coopip/djangoproject']
for path in NeededPaths:
    if(not path in sys.path):
        sys.path.append(path)
#************************END   Add Include Files to Path************************
import COOPIP_Ajax, COOPIP_Misc, COOPIP_DynamicTemplate, COOPIP_Form, COOPIP_FormCISReport
import COOPIP_Authentication
from djangoproject.models import *
import os, csv, shutil, datetime, time, reportlab, djangoproject.settings, random
from django.contrib.gis.db import models
from django.contrib.gis.geos import *
from django.http import HttpResponse
from django.contrib import admin
from django.template import Context, loader
import djangoproject.settings
import _mysql as mysql
from models import *
from django import forms
import CIS, AMR, GIS
import os, time


from django.db import transaction



@transaction.commit_on_success
def MainMenu(request):
    """MainMenu is the main selection screen for CIS Functions."""
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/cismainmenu.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    t = loader.get_template('/opt/coopip/djangoproject/templates/cismainmenu.html')
    c = Context({'LoginStatus': LoginStatus})
    return HttpResponse(t.render(c))

@transaction.commit_on_success
def InactiveCapitalCredit(request):
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission) or True: #********************************This report is disabled as it runs very slowly and needs debug disabled************* 
        t = loader.get_template('/opt/coopip/djangoproject/templates/cisinactivecapitalcredit.html')
        c = Context({'LoginStatus' : LoginStatus})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Temp=[]
    Temp.append('Mbrno| Name| Coaddr| addr1| addr2| addr3| Zip |'+ 
                'Margins| Bad Debt| Net Margins|deadpatron|DiscDate|Mbrfee|Year_ims')
    CapitalCreditMasters=CIS.CcvCapcrdmstr.objects.filter(amt1__gt=0)
    for capitalCreditMaster in CapitalCreditMasters:
        capitalCreditDetails=CIS.CcvCapcrddetl.objects.filter(mbrno=capitalCreditMaster.mbrno).filter(amt1__gt=0).order_by('year_ims')
        memberdetl=CIS.Memberdetl.objects.filter(mbrno=capitalCreditMaster.mbrno)
        if(not memberdetl):
            pass
        else:
            memberdetl=memberdetl[0]
        BadDebt=CIS.Baddebtdetl.objects.filter(mbrno=capitalCreditMaster.mbrno)
        BadDebtAmount=0.0
        for i in BadDebt:
            BadDebtAmount = BadDebtAmount + i.net
        RemainingBadDebtAmount = BadDebtAmount
        for capitalCreditDetail in capitalCreditDetails:
            if(RemainingBadDebtAmount > 0):
                NetMargin=capitalCreditDetail.amt1 - RemainingBadDebtAmount
                RemainingBadDebtAmount = RemainingBadDebtAmount - capitalCreditDetail.amt1
            else:
                NetMargin=capitalCreditDetail.amt1
                   
            if(NetMargin < 0): 
                NetMargin=0
            if(not memberdetl):
                discdate = "No memberdetl"
                mbrfee = "No memberdetl"
            else: 
                discdate = memberdetl.discdate
                mbrfee = memberdetl.mbrfee
            Temp.append(str(capitalCreditMaster.mbrno).strip() + '|' + str(capitalCreditMaster.name).strip() + '|' + 
                        str(capitalCreditMaster.coaddr).strip() + '|' +
                        str(capitalCreditMaster.addr1).strip() + '|' + str(capitalCreditMaster.addr2).strip() + '|' + 
                        str(capitalCreditMaster.addr3).strip() + '|' + 
                        str(capitalCreditMaster.zip).strip() + '|' + str(capitalCreditDetail.amt1 / 100.0).strip() + '|' + 
                        str(BadDebtAmount / 100.0).strip() + '|' +
                        str(NetMargin / 100.0).strip() + '|' + str(capitalCreditMaster.deadpatron) + '|' + 
                        str(discdate) + '|' + str(mbrfee) + '|' + str(capitalCreditDetail.year_ims))
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/cisinactivecapitalcredit.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted})
    return HttpResponse(t.render(c))

@transaction.commit_on_success
def OutdoorLightingConversionOverview(request):
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/cismainmenu.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Temp = []
    Circuits = []
    OutdoorLightingConversionDict={}
    for i in list(OutdoorLightingConversion.objects.all()):
        Raw = i.giscircuitnumbers
        RawList = Raw.replace(' ','').split(',')
        for entry in RawList:
            if not entry in Circuits:
                Circuits.append(entry)
        OutdoorLightingConversionDict[i]=RawList
    """ Circuits# Condition#; percentage of conditions marked idle """
    Temp.append('Circuit'.ljust(30) + '# Conditions'.ljust(30) + '% Idle'.ljust(30))
    Circuits.sort()
    for circuit in Circuits:
        Row = []
        Row.append(circuit)
        ConditionsAtCircuit = 0
        ConditionsAtCircuitIdle = 0
        CISLessThanConditions = 0
        CISEqualsConditions = 0
        CISGreaterThanConditions = 0
        VisitedCISLocations = []
        for condition in OutdoorLightingConversionDict:
            if not circuit in OutdoorLightingConversionDict[condition]:
                continue
            ConditionsAtCircuit+=1
            if condition.gisoutdoorlightidleindicator:
                ConditionsAtCircuitIdle+=1
            #if condition lacks data, try to fill in from AMR based on meter
            if condition.cislocation:
                CurrentLocation = condition.cislocation
            elif condition.gisoutdoorlightmeternumber:
                try:
                    Meter = AMR.Serialnumber.objects.filter(serialnumber=condition.gisoutdoorlightmeternumber)[0]
                    CurrentLocation = Meter.premiseid
                except:
                    continue
            else:
                continue
            CurrentLocation = CurrentLocation.rjust(16,'0')
            #if multiple conditions at location, skip; multiples handled below by hitting database
            if CurrentLocation in VisitedCISLocations:
                continue
            CISLocation = CIS.Locinfodetl.objects.filter(location=CurrentLocation)[0]
            VisitedCISLocations.append(CurrentLocation)
            if condition.cislocation:
                ConditionsAtLocation = OutdoorLightingConversion.objects.filter(cislocation=condition.cislocation).count()
            elif condition.gisoutdoorlightmeternumber:
                ConditionsAtLocation = OutdoorLightingConversion.objects.filter(gisoutdoorlightmeternumber = condition.gisoutdoorlightmeternumber).count()
            #get yardlight count (old style) from CIS
            YardLightNumber = 1
            while (YardLightNumber < 5):
                YardLightString = str(eval("CISLocation.yardlightinfo_00" + str(YardLightNumber))[:20])
                YardLightNumber = YardLightNumber + 1
                Quantity = int(YardLightString[10:14])
                if(not Quantity):
                    continue
            CISYardLightsAtLocation = Quantity
            ConditionsAtLocation = OutdoorLightingConversion.objects.filter(cislocation = condition.cislocation).count()
            # compare these values to determine outcome if billing completed condition right now
            if CISYardLightsAtLocation > ConditionsAtLocation:
                CISGreaterThanConditions +=1
            elif CISYardLightsAtLocation == ConditionsAtLocation:
                CISEqualsConditions +=1
            else:
                CISLessThanConditions += 1

            #CISSecurityLightsAtLocation = CIS.Seclightdetl.objects.filter(location=condition.cislocation).count()
            #if not condition.gisidleindicator and CISLocation.idlecode == '00':
            #    raise Exception('idle in cis, not condition')
        
        Row.append(ConditionsAtCircuit)
        IdlePercentage = str(float(ConditionsAtCircuitIdle) / float(ConditionsAtCircuit) * 100) + '%'
        Row.append(IdlePercentage)
        Row.append(CISLessThanConditions)
        Row.append(CISEqualsConditions)
        Row.append(CISGreaterThanConditions)
        #CISSecurityLightsInCycle
        #CISYardLightsInCycle
        RowString = ''
        for i in Row:
            RowString+=str(i).ljust(30)
        Temp.append(RowString)
        


    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted})
    return HttpResponse(t.render(c))


@transaction.commit_on_success
def ActiveMetersByCounty(request):
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/cisactivemetersbycounty.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Temp = []
    Meters=list(CIS.Meterdetl.objects.filter(idlecode='00').filter(inventorycode='02'))
    LocationInfoDetails=list(CIS.Locinfodetl.objects.all())
    LocationInfoDetailDict={}
    for i in LocationInfoDetails:
        LocationInfoDetailDict[str(i.location)] = i

    Temp.append('Meter Number'.ljust(20) + 'Idle Code'.ljust(20) + 'Inventory Code'.ljust(20) + 'Location'.ljust(20) + 'County Code'.ljust(20))
    for meter in Meters:
        location=LocationInfoDetailDict[str(meter.location)]
        Temp.append(str(meter.meter).ljust(20) + str(meter.idlecode).ljust(20) + str(meter.inventorycode).ljust(20) + 
                    str(meter.location).ljust(20) + str(location.countycode).ljust(20))

    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/cisactivemetersbycounty.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted})
    return HttpResponse(t.render(c))

@transaction.commit_on_success
def MailCodeList(request):
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/cismailcodelist.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Temp = []
    Temp.append('Report 1')
    SQLCode = """SELECT CAV_MEMBERDETL.MBRSEP, CAV_MEMBERDETL.CYCLE, CAV_MEMBERDETL.MAILCODE, CAV_MEMBERDETL.MBRFEE 
		FROM CAV_MEMBERDETL 
		GROUP BY CAV_MEMBERDETL.MBRSEP,  CAV_MEMBERDETL.CYCLE, CAV_MEMBERDETL.MAILCODE, CAV_MEMBERDETL.MBRFEE
		HAVING (((CAV_MEMBERDETL.MAILCODE)<>00) AND ((CAV_MEMBERDETL.MBRFEE)=500)) 
			OR (((CAV_MEMBERDETL.MAILCODE)=00) AND ((CAV_MEMBERDETL.MBRFEE)>500));"""
    import django.db
    Cursor=django.db.connections['cis'].cursor()
    Cursor.execute(SQLCode)
    Rows=Cursor.fetchall()
    Temp.append('MBRSEP'.ljust(20) + 'CYCLE'.ljust(20) + 'MAILCODE'.ljust(20) + 'MBRFEE'.ljust(20))
    for i in Rows:
        StringRow=''
        for j in i:
            StringRow = StringRow + str(j).ljust(20)
        Temp.append(StringRow)
    Temp.append(' ')
    Temp.append('Report 2')

    SQLCode="""SELECT CAV_MEMBERDETL.MBRSEP, CAV_MEMBERDETL.MBRFEE, CAV_MEMBERDETL.MFPAIDDATE, CAV_MEMBERDETL.MFREFDATE, CAV_MEMBERDETL.MAILCODE, CAV_MEMBERDETL.DISCDATE
		FROM CAV_MEMBERDETL
		WHERE (((CAV_MEMBERDETL.MBRFEE)=0) AND ((CAV_MEMBERDETL.MAILCODE)=00) AND ((CAV_MEMBERDETL.DISCDATE)=000000));"""
    Cursor=django.db.connections['cis'].cursor()
    Cursor.execute(SQLCode)
    Rows=Cursor.fetchall()
    Temp.append('MBRSEP'.ljust(20) + 'MBRFEE'.ljust(20) + 'MFPAIDDATE'.ljust(20) + 'MFREEDATE'.ljust(20) + 'MAILCODE'.ljust(20) + 'DISCDATE'.ljust(20))
    for i in Rows:
        StringRow=''
        for j in i:
            StringRow = StringRow + str(j).ljust(20)
        Temp.append(StringRow)
    Temp.append(' ')
    Temp.append('Report 3')

    SQLCode="""SELECT CAV_MEMBERDETL.MBRSEP, CAV_MEMBERDETL.CYCLE, CAV_MEMBERDETL.MAILCODE, CAV_MEMBERDETL.MBRFEE
                  FROM CAV_MEMBERDETL
                  GROUP BY CAV_MEMBERDETL.MBRSEP, CAV_MEMBERDETL.CYCLE, CAV_MEMBERDETL.MAILCODE, CAV_MEMBERDETL.MBRFEE
                  HAVING (((CAV_MEMBERDETL.CYCLE)>'0009' And (CAV_MEMBERDETL.CYCLE)<'0204') AND ((CAV_MEMBERDETL.MAILCODE)<>'00' Or (CAV_MEMBERDETL.MAILCODE)='01') AND ((CAV_MEMBERDETL.MBRFEE)=500)) OR (((CAV_MEMBERDETL.CYCLE)>'0009' And (CAV_MEMBERDETL.CYCLE)<'0204') AND ((CAV_MEMBERDETL.MAILCODE)='00' Or (CAV_MEMBERDETL.MAILCODE)='01') AND ((CAV_MEMBERDETL.MBRFEE)>500));"""
    Cursor=django.db.connections['cis'].cursor()
    Cursor.execute(SQLCode)
    Rows=Cursor.fetchall()
    Temp.append('MBRSEP'.ljust(20) + 'CYCLE'.ljust(20) + 'MAILCODE'.ljust(20) + 'MBRFEE'.ljust(20))
    for i in Rows:
        StringRow=''
        for j in i:
            StringRow = StringRow + str(j).ljust(20)
        Temp.append(StringRow)
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/cismailcodelist.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted})
    return HttpResponse(t.render(c))

@transaction.commit_on_success
def MeterTypesFromAMR(request):
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/cismetertypesfromamr.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Temp = []
    Temp.append('Serial Number'.ljust(20) + 'Meter Type'.ljust(20) + 'Rate Class'.ljust(20))
    Meters=AMR.Meteraccts.objects.all().order_by('serialnumber')
    for meter in Meters:
        Temp.append(str(meter.serialnumber).ljust(20) + meter.metertype.ljust(20) + meter.meterrateclass.ljust(20))
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/cismetertypesfromamr.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted})
    return HttpResponse(t.render(c))

@transaction.commit_on_success
def SSNSearch(request):
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISRestrictedUser'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/cisssnsearch.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    form=COOPIP_Form.BuildDynamicForm(request, 'SSNSearch')
    MemberNumber=COOPIP_FormCISReport.ProcessSSNSearchForm(form)
    MemberNumber=MemberNumber.rjust(8, '0')
    form=COOPIP_FormCISReport.PopulateSSNSearchForm(form)
    #Perform Data Gathering Tasks
    ljustvalue=25
    Temp = []
    Temp.append('Mbrno'.ljust(ljustvalue) + 'Mbrsep'.ljust(ljustvalue) + 'SSN'.ljust(ljustvalue) + 'SSNSPE'.ljust(ljustvalue))
    try:
        FullListing=[]
        SSNs=[]
        if(not (MemberNumber == '00000000')):
            InitialMembers=CIS.Memberdetl.objects.filter(mbrno=MemberNumber.rjust(8, '0'))
        else:
            InitialMembers=[]
        for i in InitialMembers:
            Temp.append(i.mbrno.ljust(ljustvalue) + i.mbrsep.ljust(ljustvalue) + i.ssn.ljust(ljustvalue) + i.ssnspe.ljust(ljustvalue))

        InitialDebts=CIS.Baddebtdetl.objects.filter(mbrno=MemberNumber.rjust(8, '0'))
        for i in InitialDebts:
            Temp.append(i.mbrno.ljust(ljustvalue) + i.mbrsep.ljust(ljustvalue) + i.ssn.ljust(ljustvalue) + i.ssnspe.ljust(ljustvalue))
       
        NewApplicants=CIS.Newappldetl.objects.filter(mbrno=MemberNumber.rjust(8, '0'))
        for i in NewApplicants:
            Temp.append(i.mbrno.ljust(ljustvalue) + i.mbrsep.ljust(ljustvalue) + i.ssn.ljust(ljustvalue) + i.ssnspe.ljust(ljustvalue))

        #CCVCCSSNDetails=CIS.CcvCcssndetl.objects.filter(mbrno=MemberNumber.rjust(8, '0'))
        CCVCCSSNDetails=CIS.Ccssndetl.objects.filter(mbrno=MemberNumber.rjust(8, '0'))
        for i in CCVCCSSNDetails:
            Temp.append(i.mbrno.ljust(ljustvalue) + ('SSN:' +i.ssn).ljust(ljustvalue) + ('SS2: '+ i.ss2).ljust(ljustvalue) + ('BID:' + i.bid).ljust(ljustvalue))
        
        
        for i in FullListing: 
            Temp.append(i.mbrno + '   ' + i.mbrsep + '   ' + i.ssn + '   ' + i.ssnspe)
    except IOError:
        Temp.append('')
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/cisssnsearch.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'form':form})
    return HttpResponse(t.render(c))

def GetDISCDATEValue(DISCDATE):
    if(DISCDATE[0] in ['0', '1', '2', '3', '4', '5']):
        Year='20' + DISCDATE[0:1]
    else:
        Year='19' + DISCDATE[0:1]
    YearValue=int(Year) * 100000
    MonthValue=int(DISCDATE[2:3]) * 100
    DayValue=int(DISCDATE[4:])
    return YearValue + MonthValue + DayValue

@transaction.commit_on_success
def IdleMeter(request):
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/cisidlemeter.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))    
    #Perform Data Gathering Tasks
    import django.db
    Temp=[]   
    MemberDetails=list(CIS.Memberdetl.objects.filter(cycle__in=['0900', '0901','0902']))
    MeterDetails=list(CIS.Meterdetl.objects.all())
    MeterDetailByMemberNo1={}
    for i in MeterDetails:
        MeterDetailByMemberNo1[i.mbrsep]=i
    MemberDetails2=[]
    for i in MemberDetails:
        if not i.mbrsep in MeterDetailByMemberNo1:
            # memberdetl object does not have a meter associated with it, so would not show up in idle meter report
            continue
        MeterDetail=MeterDetailByMemberNo1[i.mbrsep]
        if(MeterDetail.idlecode == '01' and MeterDetail.inventorycode in ['00','01']):
            MemberDetails2.append(i)
    NameList=list(CIS.Mbrsepmstr.objects.all())
    NameByMbrSep={}
    for i in NameList:
        NameByMbrSep[i.mbrsep]=i.name.ljust(30)
    LocationDictionary={}
    for i in MemberDetails2:
        MBRSEP=i.mbrsep.ljust(20)
        CYCLE=i.cycle.ljust(15)
        NAME=NameByMbrSep[i.mbrsep]
        if i.mbrsep in MeterDetailByMemberNo1:
            LOCATION=MeterDetailByMemberNo1[i.mbrsep].location.ljust(20)
        else:
            LOCATION='UNKNOWN'
        QueryableLocation=MeterDetailByMemberNo1[i.mbrsep].location.rjust(16, '0')
        DISCDATE=i.discdate.ljust(20)
        if QueryableLocation in LocationDictionary:
            DISCDATEValue1=GetDISCDATEValue(DISCDATE)
            DISCDATEValue2=GetDISCDATEValue(LocationDictionary[QueryableLocation][0])
            if(DISCDATEValue1 > DISCDATEValue2):
                LocationDictionary[QueryableLocation] = [DISCDATE, MBRSEP + CYCLE + NAME + LOCATION + DISCDATE] 
        else:
            LocationDictionary[QueryableLocation]=[DISCDATE, MBRSEP + CYCLE + NAME + LOCATION + DISCDATE]
    LocationList2=list(CIS.Locationdetl.objects.all().order_by('discdate'))
    LocationDetailByQueryableLocation={}
    for i in LocationList2:
        if i.location in LocationDetailByQueryableLocation:
            continue        
        LocationDetailByQueryableLocation[i.location]=i
    NotFoundCount = 0
    for queryableLocation in LocationDictionary:
        if queryableLocation[:16] in LocationDetailByQueryableLocation:
            location=LocationDetailByQueryableLocation[queryableLocation[:16]]
        else:
            Temp.append('Location Not Found:  ' + str(queryableLocation))
            NotFoundCount = NotFoundCount + 1
        if(not (location.discdate == '000000')):
            Temp.append(LocationDictionary[queryableLocation][1])
    Temp.sort()
    Count=len(Temp) - NotFoundCount
    Temp.insert(0, 'MBRSEP'.ljust(20) + 'CYCLE'.ljust(15) + 'NAME'.ljust(30) + 'LOCATION'.ljust(20) + 'DISCDATE'.ljust(20))
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/cisidlemeter.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'MeterTotal':Count})
    return HttpResponse(t.render(c))


@transaction.commit_on_success
def CISMetersPossiblyNotJunked(request):
    import django.db
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Temp=[]
    MemberDetails=CIS.Memberdetl.objects.filter(cycle='0902') | CIS.Memberdetl.objects.filter(cycle='0901') | CIS.Memberdetl.objects.filter(cycle='0900')
    #This report gets a list of meters that meet the following criteria:
    #A New Meter has been placed at the location that the old meter is associated with (based on map number)
    #The meter is not active anywhere on the system
    #The meter is not in junk status
    Meters=list(CIS.Meterdetl.objects.all().exclude(junkedcode='01').filter(deletecode='00').filter(location='0000000000000000'))
    MetersNotAtLocation=[]
    Temp.append('Location'.ljust(20) + 'Mbrsep'.ljust(20) + 'JunkedCode'.ljust(12) + 'Number'.ljust(15)) 
    for meter in Meters:
        Temp.append(meter.location.ljust(20) + meter.mbrsep.ljust(20) + meter.junkedcode.ljust(12) + meter.meter.ljust(15)) 

#        Location=CIS.Locationdetl.objects.filter(location=meter.location)
 #       if(not Location.meter = 
  #      LocationMeters=CIS.Meterdetl.objects.filter(location=meter.location)
    
    Temp.append(str(len(Meters)))
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericReportTitle='CIS Meters Possibly Not Junked'
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))




@transaction.commit_on_success
def CISReportAbnormalPenalty(request):
    import django.db
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Temp=[]
    Temp.append('Assumes a penalty for all accounts, even ones that do not need one')
    PreviousBillMonthYear='1301'
    BillMonthYear='1302'
    Temp.append('Bill Month Year:  ' + BillMonthYear)
    Temp.append('Previous Bill Month Year:  ' + PreviousBillMonthYear)
    Mbrhistdetls=list(CIS.Mbrhistdetl.objects.filter(billmoyr=BillMonthYear).order_by('mbrsep'))
    Temp.append('Member Sep'.ljust(25) + 'Cycle'.ljust(20) + 'Previous Bill'.ljust(25) + 'Actual Penalty'.ljust(25) + 'Calculated Penalty'.ljust(25) + 'Difference'.ljust(25))
    for i in Mbrhistdetls:
        ActualPenalty=i.penalty
        try:
            bill = CIS.Billhistdetl.objects.filter(mbrsep=i.mbrsep).filter(billmoyr=PreviousBillMonthYear)[0]
            PreviousBillAmount = bill.fuel + bill.energy + bill.roundup + bill.tax + bill.localtax + bill.miscchg + bill.arrears
            Cycle = bill.cycle
        except IndexError:
            PreviousBillAmount = 0
    
        CalculatedPenalty = int(PreviousBillAmount * 0.015)
        Difference = ActualPenalty - CalculatedPenalty
        if(Difference > 0 and CalculatedPenalty > 0):
            Temp.append(str(i.mbrsep).ljust(25) + str(Cycle).ljust(25) + str(PreviousBillAmount).ljust(25) + str(ActualPenalty).ljust(25) + str(CalculatedPenalty).ljust(25) + str(Difference).ljust(25))
          
    

    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericReportTitle='CIS Abnormal Penalty Report'
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))




@transaction.commit_on_success
def CISRatesWithTaxes(request):
    import django.db
    GenericReportTitle='COOPIP CIS Rates With Taxes'
    GenericMessage='You do not have permission to this application.'
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    Temp=[]
    #Perform Data Gathering Tasks
    #Need to list anything with:
    #Rate 1, Tax Code != 10
    #Rate 10, Tax Code != 10
    #Rate 15, Tax Code != 10
    #Rate 14, Tax Code = 10
    #Pull MeterGuide, Active Accounts Only
    NotTaxedRates=['0001', '0010', '0015', '0012']
    for rate in NotTaxedRates:
        Temp.append('')
        Temp.append('')
        Temp.append('Rate:  ' + rate)
        Temp.append('Member Sep'.ljust(20) + 'Rate'.ljust(20) + 'Tax Dist Code'.ljust(20) + 'MeterGuide'.ljust(40) + 'GIS Service Type'.ljust(40))
        Members=CIS.Memberdetl.objects.filter(rate=rate).filter(discdate='000000').exclude(taxdistcode='10')
        for member in Members:
            try:
                MeterGuide=CIS.Locinfodetl.objects.filter(location=member.location)[0].comments
            except:
                MeterGuide='Unknown'
            try:
                MeterGuide=CIS.Locinfodetl.objects.filter(location=member.location)[0].comments
            except:
                MeterGuide='Unknown'
            try:
                temp = member.location
                GISMeter=Assembly.objects.filter(MeterLocationNumberFromCIS__icontains=str(member.location).strip('0'))[0]
                if(GISMeter.MeterServiceType):
                    GISServiceType=GISMeter.MeterServiceType.Name
                else:
                    GISServiceType="None"
            except:
                GISServiceType='No Match Found in GIS'

            Temp.append(member.mbrsep.ljust(20) + member.rate.ljust(20) + member.taxdistcode.ljust(20) + MeterGuide.ljust(40) + GISServiceType.ljust(40))
    TaxedRates=['0014']
    for rate in TaxedRates:
        Temp.append('')
        Temp.append('')
        Temp.append('Rate:  ' + rate)
        Temp.append('Member Sep'.ljust(20) + 'Rate'.ljust(20) + 'Tax Dist Code'.ljust(20) + 'MeterGuide'.ljust(40))
        Members=CIS.Memberdetl.objects.filter(rate=rate).filter(discdate='000000').filter(taxdistcode='10')
        for member in Members:
            try:
                MeterGuide=CIS.Locinfodetl.objects.filter(location=member.location)[0].comments
            except:
                MeterGuide='Unknown'
            try:
                temp = member.location
                GISMeter=Assembly.objects.filter(MeterLocationNumberFromCIS__icontains=str(member.location).strip('0'))[0]
                if(GISMeter.MeterServiceType):
                    GISServiceType=GISMeter.MeterServiceType.Name
                else:
                    GISServiceType="None"
            except:
                GISServiceType='No Match Found in GIS'
            Temp.append(member.mbrsep.ljust(20) + member.rate.ljust(20) + member.taxdistcode.ljust(20) + MeterGuide.ljust(40) + GISServiceType.ljust(40))
    
    Cycles=['0203', '0201']
    for cycle in Cycles:
        Temp.append('')
        Temp.append('')
        Temp.append('Cycle:  ' + cycle)
        Members=CIS.Memberdetl.objects.filter(cycle=cycle).filter(discdate='000000').filter(taxdistcode='10')
        for member in Members:
            try:
                MeterGuide=CIS.Locinfodetl.objects.filter(location=member.location)[0].comments
            except:
                MeterGuide='Unknown'
            try:
                temp = member.location
                GISMeter=Assembly.objects.filter(MeterLocationNumberFromCIS__icontains=str(member.location).strip('0'))[0]
                if(GISMeter.MeterServiceType):
                    GISServiceType=GISMeter.MeterServiceType.Name
                else:
                    GISServiceType="None"
            except:
                GISServiceType='No Match Found in GIS'
            Temp.append(member.mbrsep.ljust(20) + member.rate.ljust(20) + member.taxdistcode.ljust(20) + MeterGuide.ljust(40) + GISServiceType.ljust(40))
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))



@transaction.commit_on_success
def LargeBalanceActive(request):
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/cislargebalanceactive.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Temp=[]
    Temp.append('Member Sep'.ljust(25) + 'Name'.ljust(35) + 'Location'.ljust(35) + 'Current Balance Due'.ljust(25) + 'Past Due Balance'.ljust(25)+
                'Bill Date'.ljust(12) + 'Cycle'.ljust(15) + 'OpenArrangement'.ljust(15))
    #.filter(net__gte=49999).
    import datetime
    TwoDigitYear=str(datetime.datetime.today().year)[2:]
    TwoDigitMonth=str(datetime.datetime.today().month - 1).rjust(2, '0') 
    if(TwoDigitMonth == '00'):
        TwoDigitYear = str(datetime.datetime.today().year-1)[2:]
        TwoDigitMonth = '12'
    MaxYear='60'
    EarliestDate=int(TwoDigitYear + TwoDigitMonth + str(datetime.datetime.today().day)[2:])  #Does not need to be a valid date as comparison is integer based
    LatestDate=int(MaxYear + '0000')
    MemberDetails = list(CIS.Mbrhistdetl.objects.filter(billdate__gte=EarliestDate).filter(billdate__lte=LatestDate).filter(net__gte=50000)\
                    .filter(arrears__gte=10000).order_by('mbrsep', "-billdate"))
    MemberSepList=[]
    for i in MemberDetails:
        try:
            CurrentLocation=CIS.Locationdetl.objects.filter(location=i.location).order_by('-conndate')[0]
            Name=CurrentLocation.name
            Location=CurrentLocation.location
            LocationInfoDetail=CIS.Locinfodetl.objects.filter(location=Location)[0]
            Cycle=LocationInfoDetail.cycle
        except:
            CurrentLocation=None
            Location="Unknown"
            Name="Unkown"
            Cycle="Unknown"
        if( (not CurrentLocation or CurrentLocation.discdate == '000000') and (not i.mbrsep in MemberSepList) ):
            MemberSepList.append(i.mbrsep)
            MemberDetail=CIS.Memberdetl.objects.filter(mbrsep=i.mbrsep).get()
            MemberNo=i.mbrsep[:-2]
            PaymentArrangementsIndicator=bool(CIS.Arrangedetl.objects.filter(completedate='000000').filter(mbrno=MemberNo))
            if(PaymentArrangementsIndicator):
                PaymentString='Payment Arrangements'
            else:
                PaymentString = ''
            Temp.append(str(i.mbrsep).ljust(25) + str(Name).ljust(35) + str(Location).ljust(35) + str(MemberDetail.amount / 100).ljust(25) + 
                        str(i.arrears / 100).ljust(25) + str(i.billdate).ljust(12) + str(Cycle).ljust(15) + PaymentString.ljust(15))
    #Billing needs a report that would provide a list of active accounts that have arrears greater than $500.00 in a past due balance.
    #Fields:
    #Account #
    #Name
    #Location #
    #Current Balance
    #Past Due Balance
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/cislargebalanceactive.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted})
    return HttpResponse(t.render(c))




@transaction.commit_on_success
def AbnormalMeterReading(request):
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/cisabnormalmeterreading.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))    
    #Perform Data Gathering Tasks
    Temp=[]

    #Groups will be defined based on usage patterns
    #based on January, April, July, and October readings
    #All readings are relative
    #High January, High July = Electric Heat Air Conditioning
    #High January = Electric Heat
    #High July = Air Conditioning
    #Not Full Year = Unknown
    #Other groups needed?  Do analysis
    Temp.append('DCSI Number'.ljust(25) + 'Group'.ljust(25) )
    CapitalCreditMasters=list(CIS.CcvCapcrdmstr.objects.filter(amt1__gt=0))
    for capitalCreditMaster in CapitalCreditMasters:
        capitalCreditDetails=CIS.CcvCapcrddetl.objects.filter(mbrno=capitalCreditMaster.mbrno).filter(amt1__gt=0).order_by('year_ims')
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/cisabnormalmeterreading.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted})
    return HttpResponse(t.render(c))



@transaction.commit_on_success
def OperationRoundupNumberOfMembers(request):
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/cisoperationroundup.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
      #Perform Data Gathering Tasks
    Preformatted = "Number of Members participating in Operation Roundup:\n"
    a = list(CIS.Mbrsepmstr.objects.filter(paytype='00'))
    b = 0
    for i in a:
        if(i.acctstatus[0]=='A'):
            b = b + 1
    Preformatted = Preformatted + str(b) + "\n"
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/cisoperationroundup.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted})
    return HttpResponse(t.render(c))
   

def MeterReport(request):
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/cismeterreport.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Temp=[]
    Temp.append("Meter, Location, Member Sep, Inventory Code, Idle Code, Rate, Delete Code, Junk Code, Name, Disc Date")
    Meters=list(CIS.Meterdetl.objects.all())
    LocationDetails=list(CIS.Locationdetl.objects.all())
    LocationDetailsDict={}
    for i in LocationDetails:
        LocationDetailsDict[str(i.location)] = i
    LocationInfoDetails=list(CIS.Locinfodetl.objects.all())

    LocationInfoDetailsDict={}
    for i in LocationInfoDetails:
        LocationInfoDetailsDict[str(i.location)] = i



    for meter in Meters:
        try:
            locationdetl=LocationDetailsDict[str(meter.location)]
        except:
            locationdetl=None        
        if(locationdetl):
            locationInfoDetail=LocationInfoDetailsDict[str(locationdetl.location)]
            if(locationdetl):
                rate = locationInfoDetail.rate
                name = locationdetl.name
                discdate=locationdetl.discdate
            else:
                rate = "UNKNOWN LOCATION DETAIL"
                name = "UNKNOWN NAME"
                discdate="UNKNOWN DISCDATE"
        else:
            rate = "UNKNOWN LOCATION"
            name = "UNKNOWN NAME"
            discdate = "UNKNOWN DISCDATE"
       
        Temp.append(str(meter.meter) + ',' + str(meter.location) + ',' + 
                    str(meter.mbrsep) + ',' + str(meter.inventorycode) + ',' + str(meter.idlecode) + ',' + str(rate) +
                    ',' + str(meter.deletecode) + ',' + str(meter.junkedcode) + ',' + name + ',' + discdate)
    Preformatted = """Inventory Code 00 = Idle not in field
                      Inventory Code 01 = Idle in Field
                      Inventory Code 02 = Active
                      Inventory Code 03 = Bill Discountinued, Meter Active
                      Inventory Code 04 = Service is disconnected at transformer, but meter is still in field
                      Inventory Code 05 = Service is disconnected at transformer, but meter is removed from site

                      Idle Code 00 = Active
                      Idle Code 01 = Inactive\n""" + "\n".join(Temp)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/cismeterreport.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted})
    return HttpResponse(t.render(c))




@transaction.commit_on_success
def AMRFixMeterType(request):
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/amrfixmetertype.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Temp = []
    import django.db
    #Cursor=django.db.connections['amr'].cursor()
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='IMT3H' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=21 AND MODEL=52);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='120V' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=31 AND MODEL=66);")   
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='14.4KWH' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=31 AND MODEL=68);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='12.0KWH' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=31 AND MODEL=69);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='14.4KWH' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=31 AND MODEL=70);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='CENTRON' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=34 AND MODEL=160);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='S4F6S' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=37 AND MODEL=1);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='S4F9S' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=37 AND MODEL=3);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='S4F6S' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=37 AND MODEL=4);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='S4F5S' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=37 AND MODEL=5);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='S4F15' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=37 AND MODEL=9);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='S415K' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=37 AND MODEL=13);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='S4F9S' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=37 AND MODEL=67);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='S4F5S' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=37 AND MODEL=69);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='S4F15' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=37 AND MODEL=73);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='S415K' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=37 AND MODEL=77);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='DEFAULT' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=40 AND MODEL=0);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='FOCUS' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=42 AND MODEL=131);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='FOCUSCLS20' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=42 AND MODEL=135);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='AL0.6' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=45 AND MODEL=145);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='ALTIMUS' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=45 AND MODEL=225);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='CENTRON' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=75 AND MODEL=130);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='FOCUSCLS20' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=84 AND MODEL=132);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='FOCUS' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=84 AND MODEL=134);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='CENTRON 2.0' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=95 AND MODEL=2);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='UMT_2.10' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=97 AND MODEL=2);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='DEFAULT' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=100 AND MODEL=16);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='UMT_2.10' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=109 AND MODEL=2);")
    #Cursor.execute("UPDATE METERACCTS SET METERTYPE='UMT_2.10' WHERE SERIALNUMBER IN ( SELECT SERIALNUMBER FROM SERIALNUMBER WHERE TYPE=117 AND MODEL=2);")
    #Cursor.execute("COMMIT;")
    #Preformatted="Completed Attempt to Run SQL Code on the AMR Server.  Check the Meter Countys by Type/Model Report to confirm that all are correct."
    Preformatted="This script updates AMR MeterAccounts MeterTypes.  It is currently disabled, please contact the Technology Department to have it enabled."
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/amrfixmetertype.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted})
    return HttpResponse(t.render(c))


@transaction.commit_on_success
def CISReportNationality(request):
    import django.db
    GenericReportTitle='COOPIP CIS Report Nationality'
    GenericMessage='You do not have permission to this application.'
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))

    Temp=[]
    Temp.append('This is a list of members with a Nationality Code of 00, Members with cycles of 0901 and 0902 are excluded')
    Temp.append('Member Seperator'.ljust(30))
    #Perform Data Gathering Tasks
    #Get a List of Accounts with blank Memberdetl.natlcode entries
    #Members on cycle 0901 and 0902 are disconnected
    MemberDetails=list(CIS.Memberdetl.objects.all().filter(natlcode='00').exclude(cycle='0901').exclude(cycle='0902'))
    for i in MemberDetails:
        mbrsepmstr=CIS.Mbrsepmstr.objects.filter(mbrsep=i.mbrsep)[0]
        Temp.append(i.mbrsep.ljust(30) + i.dist.ljust(20) + mbrsepmstr.name.ljust(40) + str(i.telephone).ljust(15))

    Temp.append('This is a list of members with a Nationality Code of 00 or 03, districts of 10, 11, 12 and a rate of 0001.  Cycles 0901 and 0902 are excluded.')
    Temp.append('Member Seperator'.ljust(30))
    #Perform Data Gathering Tasks
    #Get a List of Accounts with blank Memberdetl.natlcode entries
    #Members on cycle 0901 and 0902 are disconnected
    MemberDetails=CIS.Memberdetl.objects.all().filter(natlcode__in=['03', '00']).filter(dist__in=['10', '11', '12']).filter(rate='0001').exclude(cycle='0901').exclude(cycle='0902')
    for i in MemberDetails:
        mbrsepmstr=CIS.Mbrsepmstr.objects.filter(mbrsep=i.mbrsep)[0]
        Temp.append(i.mbrsep.ljust(30) + i.dist.ljust(20) + mbrsepmstr.name.ljust(40) + str(i.telephone).ljust(15)) 
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))



@transaction.commit_on_success
def ResidentialSurvey2012(request):
    import django.db
    GenericReportTitle='Residential Survey 2012'
    GenericMessage='You do not have permission to this application.'
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    Temp=[]
    Temp.append('This is a list of MBRSEP, NAME, DISCDATE, Rate, MST2_MBRSEPMSTR.ADDR1, ADDR2, ADDR3, Zip, CountyCode, MST2_LOCINFODETL.ADDR1, BILLMOYR, KWH')
    #AFTER GETTING IT RUNNING, modify so that KWH is displayed as a column for each row

    Temp.append('MBRSEP'.ljust(30) + 'NAME'.ljust(40) + 'DISCDATE'.ljust(10) + 'Rate'.ljust(10) + 'ADDR1'.ljust(30) + 'ADDR2'.ljust(30) + 'ADDR3'.ljust(30) + 
                'ZIP'.ljust(12) + 'CountyCode'.ljust(20) + 'LOC_ADDR1'.ljust(30) 
                + 'KWH 1101'.ljust(10) 
                + 'KWH 1102'.ljust(10) 
                + 'KWH 1103'.ljust(10) 
                + 'KWH 1104'.ljust(10) 
                + 'KWH 1105'.ljust(10) 
                + 'KWH 1106'.ljust(10) 
                + 'KWH 1107'.ljust(10) 
                + 'KWH 1108'.ljust(10) 
                + 'KWH 1109'.ljust(10) 
                + 'KWH 1110'.ljust(10) 
                + 'KWH 1111'.ljust(10) 
                + 'KWH 1112'.ljust(10))
    #TABLES:  MST2_CAV_MBRSEPMSTR MBRSEP, NAME, DISCDATE ('000000'), Rate ('0001'), addr1, addr2, addr3, zip
    #         MST2_CAV_LOCINFODETL (join mbrsep to cav_mbrhistdetl and cav_memberdetl, join location to cav_locinfodetl)  CountyCode, ADDR1
    #MST2_CAV_MBRHISTDETL, billmoyr ('1104', '1105', '1106', '1107', '1108', '1109', '1110', '1111', '1112'
    #MST2_CAV_MBRHISTDETL, KWH  [put in a sep column based on billmoyr]
    Members = list(CIS.Mbrsepmstr.objects.all())
    MemberHistDetails=list(CIS.Mbrhistdetl.objects.filter(billmoyr__in=['1101', '1102', '1103', '1104', '1105', '1106', '1107', '1108', '1109', '1110', '1111', '1112']))
    MemberHistDetailsDict={}
    for i in MemberHistDetails:
        MemberHistDetailsDict[str(i.mbrsep) + '_' + str(i.billmoyr)] = i

    LocationInfoDetails=list(CIS.Locinfodetl.objects.all())
    LocationInfoDetailDict={}
    for i in LocationInfoDetails:
        LocationInfoDetailDict[str(i.location)] = i
    
    MemberDetails=list(CIS.Memberdetl.objects.filter(discdate='000000'))
    MemberDetailsDict={}
    for i in MemberDetails:
        MemberDetailsDict[str(i.mbrsep)] = i



    for member in Members:
        try:
            member_detl=MemberDetailsDict[str(member.mbrsep)]
        except:
            #Temp.append(str(member.mbrsep) + ' was not added, could not pull up member details dict.\n')
            continue
        try:
            member_locinfodetl=LocationInfoDetailDict[str(member_detl.location)]
        except:
            #Temp.append(str(member_detl.location) + ' was not added, could not pull up member_locinfodetl.\n')
            continue

        Row=member.mbrsep.ljust(30) 
        Row+=member.name.ljust(40)
        Row+=member_detl.discdate.ljust(10)
        Row+=member_detl.rate.ljust(10)
        Row+=member.addr1.ljust(30)
        Row+=member.addr2.ljust(30)
        Row+=member.addr3.ljust(30)
        Row+=member.zip.ljust(12)
        Row+=member_locinfodetl.countycode.ljust(20)
        Row+=member_locinfodetl.addr1.ljust(30)
        try:
            Row+=str(MemberHistDetailsDict[str(member.mbrsep) + '_1101'].kwh).ljust(10)
        except:
            Row+=''.ljust(10)
        try:
            Row+=str(MemberHistDetailsDict[str(member.mbrsep) + '_1102'].kwh).ljust(10)
        except:
            Row+=''.ljust(10)
        try:
            Row+=str(MemberHistDetailsDict[str(member.mbrsep) + '_1103'].kwh).ljust(10)
        except:
            Row+=''.ljust(10)
        try:
            Row+=str(MemberHistDetailsDict[str(member.mbrsep) + '_1104'].kwh).ljust(10)
        except:
            Row+=''.ljust(10)
        try:
            Row+=str(MemberHistDetailsDict[str(member.mbrsep) + '_1105'].kwh).ljust(10)
        except:
            Row+=''.ljust(10)
        try:
            Row+=str(MemberHistDetailsDict[str(member.mbrsep) + '_1106'].kwh).ljust(10)
        except:
            Row+=''.ljust(10)
        try:
            Row+=str(MemberHistDetailsDict[str(member.mbrsep) + '_1107'].kwh).ljust(10)
        except:
            Row+=''.ljust(10)
        try:
            Row+=str(MemberHistDetailsDict[str(member.mbrsep) + '_1108'].kwh).ljust(10)
        except:
            Row+=''.ljust(10)
        try:
            Row+=str(MemberHistDetailsDict[str(member.mbrsep) + '_1109'].kwh).ljust(10)
        except:
            Row+=''.ljust(10)
        try:
            Row+=str(MemberHistDetailsDict[str(member.mbrsep) + '_1110'].kwh).ljust(10)
        except:
            Row+=''.ljust(10)
        try:
            Row+=str(MemberHistDetailsDict[str(member.mbrsep) + '_1111'].kwh).ljust(10)
        except:
            Row+=''.ljust(10)
        try:
            Row+=str(MemberHistDetailsDict[str(member.mbrsep) + '_1112'].kwh).ljust(10)
        except:
            Row+=''.ljust(10)
        Temp.append(Row)
        
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericMessage=''
    
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))



@transaction.commit_on_success
def CISReportBoldAccountNoCheckmarks(request):
    import django.db
    GenericReportTitle='COOPIP CIS Report BoldAccountNoCheckmarks'
    GenericMessage='You do not have permission to this application.'
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    Temp=[]


    Temp.append('This is a list of bold accounts with no check marks for special meeting and newsletter')
    members=list(CIS.Membermstr.objects.all())
    for i in members:
        MemberSep=i.mbrno + i.mbrseq
        try:
            MemberDetail=CIS.Memberdetl.objects.filter(mbrsep=MemberSep)[0]
        except:
            continue
        if(not MemberDetail.mailcode == '00' and not MemberDetail.newscode == '00'):
            try:
                CycleInt=int(MemberDetail.cycle.lstrip('0'))
            except:
                continue
            if(CycleInt > 9) and (CycleInt < 206):
                Temp.append(MemberSep)


    #Remove the following member seps per Linda Wright, as they are inactive accounts that we want removed from the list manually
    #Gregory Roby 01/11/2012
    for i in Temp[:]:
        if(str(i) in ['00091925001', '00082650003', '00083133001', '00088511001', '00093752002', '00047566003', '00096066004', '00087710002',
                 '00095736001', '00084341001', '00069493001', '00084701003', '00067766001', '00063016001', '00060601001', '00023955001', '00097342001',
                 '00003417001', '00003417001', '00096746001', '00086274005', '00081331001', '00090682001', '00092822001', '00083958001',
                 '00084190001', '00056902001', '00083119002', '00082441002', '00002279004', '00060044002', '00054196001', '00094947001',
                 '00085087001', '00004524001', '00056723001', '00056723001', '00082314001', '00083583001', '00086593002', '00097888004']):
            Temp.remove(i)

    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericMessage=''
    
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))


@transaction.commit_on_success
def CISReportAverageBill(request):
    import django.db
    GenericReportTitle='COOPIP CIS Report AverageBillRate'
    GenericMessage='You do not have permission to this application.'
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    Temp=[]
    import datetime
    now=datetime.datetime.now()
 
    MonthYearStrings=[]
    PreviousYear=now.year - 1
    CurrentMonth = now.month - 1
    while(CurrentMonth <= 12):
        MonthYearStrings.append(str(PreviousYear)[2:] + str(CurrentMonth).rjust(2, '0')) 
        CurrentMonth = CurrentMonth + 1
    CurrentMonth = 1
    CurrentYear = PreviousYear + 1
    while(CurrentMonth < now.month):
        MonthYearStrings.append(str(CurrentYear)[2:] + str(CurrentMonth).rjust(2, '0')) 
        CurrentMonth = CurrentMonth + 1
    MemberSepDictionary={}

    for monthyearstring in MonthYearStrings:
        Bills=list(CIS.Billhistdetl.objects.filter(billmoyr = monthyearstring).filter(rate='0001'))
        for bill in Bills:
            if not bill.mbrsep in MemberSepDictionary:
                MemberSepDictionary[bill.mbrsep] = []
            MemberSepDictionary[bill.mbrsep].append([bill.rate, bill.kwh, bill.amount])
    Temp.append('This is a list of accounts along with their average bills for the past 12 months')
    Temp.append('Member Sep'.ljust(30) + 'KWH'.ljust(20))
    for key in MemberSepDictionary.keys():
        Count = 0
        Total = 0
        for bill in MemberSepDictionary[key]:
            Count = Count + 1
            Total = Total + bill[1]
            if(Count == 12):
                Temp.append(key.ljust(30) + str(Total / Count).ljust(20))


    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericMessage=''
    
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))



@transaction.commit_on_success
def CISReportEbillUsers(request):
    import django.db
    GenericReportTitle='COOPIP CIS Report EbillUsers'
    GenericMessage='You do not have permission to this application.'
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    Temp=[]
    Now=datetime.datetime.now()
    Month = Now.month - 1
    if(Month == 0):
        Month  = 12
        Year = Now.year - 1
    else:
        Year = Now.year
    MonthYear=str(Year)[2:] + str(Month).rjust(2, '0') 
    Temp.append('This is a list of ebill members for the month of:  ' + str(Month) + '/' + str(Year))
    Ebills=CIS.Ebilldetl.objects.filter(billmoyr=MonthYear)
    for i in  Ebills:
        Temp.append(i.mbrsep)
 
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericMessage=''
    
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))



@transaction.commit_on_success
def CISReportInvalidBoardDistrict(request):
    import django.db
    GenericReportTitle='COOPIP CIS Invalid Board District'
    GenericMessage='You do not have permission to this application.'
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    Temp=[]
    Temp.append('This is a list of locations with invalid board districts')
    Locations=CIS.Locinfodetl.objects.all().exclude(distcode='11').exclude(distcode='12').exclude(distcode='13').exclude(distcode='21')\
                  .exclude(distcode='22').exclude(distcode='23').exclude(distcode='31').exclude(distcode='32').exclude(distcode='33')
    for i in Locations:
        Temp.append(i.location.ljust(20) + i.distcode)
 
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericMessage=''
    
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))


@transaction.commit_on_success
def CISReportAnnualMeetingMailingLabel(request):
    import django.db
    GenericReportTitle='COOPIP CIS Annual Meeting Mailing Label'
    GenericMessage='You do not have permission to this application.'
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    Temp=[]
    Members=CIS.Memberdetl.objects.filter(mailcode='00').filter(discdate='000000')
    Temp.append('This is a list Members with a mailcode of 00 and a discdate of 000000.  Number of Matches:  ' + str(Members.count()))
    Temp.append('Mbrsep'.ljust(20)  + 'Name'.ljust(30) + 'Coaddr'.ljust(40) + 'Addr1'.ljust(40) + 'Addr2'.ljust(40) + 'Addr3'.ljust(40) + 'zip'.ljust(20) + 'Telephone'.ljust(12))
    for i in Members:
        MemberSepMaster = CIS.Mbrsepmstr.objects.filter(mbrsep=i.mbrsep).get()
        Temp.append(i.mbrsep.ljust(20) + MemberSepMaster.name.ljust(30) + MemberSepMaster.coaddr.ljust(40) + MemberSepMaster.addr1.ljust(40) + MemberSepMaster.addr2.ljust(40) +\
                    MemberSepMaster.addr3.ljust(40) + MemberSepMaster.zip.ljust(20) + i.telephone.ljust(12))
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))

@transaction.commit_on_success
def CISReportPrepaidMetersWithCollars(request):
    import django.db
    GenericReportTitle='COOPIP CIS Prepaid Meters With Collars'
    GenericMessage='You do not have permission to this application.'
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    Temp=[]
    PrepaidMembers=CIS.Ppmdetl.objects.all().order_by('mbrsep')
    Temp.append('Member Sep'.ljust(16) + 'Meter'.ljust(15) + 'Location'.ljust(20) + 'Matching Collar'.ljust(20) + 'Collar Status'.ljust(20) + 'Collar Type'.ljust(20) + 'PrePaid Status'.ljust(27) + \
                'AMR Serial Number'.ljust(20) + 'AMR Collar'.ljust(20) + 'Collars Match'.ljust(20))
    for i in PrepaidMembers:
        Meters=CIS.Meterdetl.objects.filter(mbrsep=str(i.mbrsep).rjust(11, '0')).exclude(idlecode='01')
        for meter in Meters:
            equiplocdetl=CIS.Equiplocdetl.objects.filter(location=meter.location)
            MatchingCollar='None'
            MatchingCollarStatus='None'
            MatchingCollarType='None'
            #****************************************************************
            #IN AMR, The matching collar is actually the one specified by the USER1 field.  Need to update the AMR side of this query if it is needed still
            #****************************************************************
            if(equiplocdetl and len(equiplocdetl) == 1):
                equipdetl=CIS.Equipdetl.objects.filter(recordid=equiplocdetl[0].recordid)
                if(equipdetl and len(equipdetl) == 1):
                    MatchingCollar=equipdetl[0].equipserial
                    MatchingCollarStatus=equipdetl[0].equipstatus
                    MatchingCollarType=equipdetl[0].equiptype
            aMRMeterAccount=AMR.Meteraccts.objects.filter(metercustomeracctnum=i.mbrsep)
            AMRSerialNumber='None'
            AMRCollar='None'
            if(aMRMeterAccount and len(aMRMeterAccount) == 1):
                AMRSerialNumber=aMRMeterAccount[0].serialnumber
                AMRSerialNumbers=AMR.Serialnumber.objects.filter(premiseid=meter.location.lstrip('0')).exclude(serialnumber=AMRSerialNumber)
                if(AMRSerialNumbers and len(AMRSerialNumbers) == 1):
                    AMRCollar=AMRSerialNumbers[0].serialnumber      
            if(str(AMRCollar) == str(MatchingCollar).lstrip('0').strip()):
                CollarsMatch = True
            else:
                CollarsMatch = False 
            Temp.append(str(i.mbrsep).ljust(16) + str(meter.meter).lstrip('0').ljust(15) + str(meter.location).lstrip('0').ljust(20) + \
                        str(MatchingCollar).ljust(20) + str(MatchingCollarStatus).ljust(20) + str(MatchingCollarType).ljust(20) + i.ppmstatus.ljust(27) + \
                        str(AMRSerialNumber).ljust(20) + str(AMRCollar).ljust(20) + str(CollarsMatch).ljust(20))
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))



@transaction.commit_on_success
def CISReportCheckByCheckNumber(request):
    import django.db
    GenericReportTitle='COOPIP CIS Check 2012 By Check Number CSV Report'
    GenericMessage='You do not have permission to this application.'
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    Temp=[]
    Checks=list(CIS.Capcrdchkdetl.objects.filter(ckdate__gte='120101').filter(ckdate__lte='130000').order_by('ckdate'))


    for check in Checks:
        Temp.append(str(check.mbrno).lstrip('0') + ',' + str(int(check.ckamt) / 100) + '.' + str(check.ckamt)[-2:]  + ',' + str(check.checkno.lstrip('0')) + ',' + str(check.ckdate))
    




    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))





@transaction.commit_on_success
def CISReportConstantBills(request):
    import django.db
    GenericReportTitle='COOPIP CIS Report - Constant Bills'
    GenericMessage='You do not have permission to use this application.'
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    Temp=[]
    MeterPercentList = []
    Members=list(CIS.Mbrsepmstr.objects.all().filter(acctstatus = 'A     '))
    Temp.append('Membersep'.ljust(20) + 'Name'.ljust(30) + 'HighestBill'.ljust(20) + 'LowestBill'.ljust(20) + 'PercentChange'.ljust(20))
    for member in Members:
        BillHistory = CIS.Billhistdetl.objects.all().filter(mbrsep = str(member.mbrsep).rjust(11,'0')).order_by('-billmoyr')
        if BillHistory.count() < 12:
            #Temp.append(member.mbrsep.ljust(20) + str(BillHistory.count()))
            continue
        BillHistory = BillHistory[:12]
        previous = BillHistory[0]
        Change = 0
        Maximum = BillHistory[0].energy
        Minimum = BillHistory[0].energy        
        Percentage = []
        BreakIndicator = False
        for bill in BillHistory[1:]:
            Change = abs(bill.energy - previous.energy)
            #Temp.append(str(Change))
            Previous = bill
            if bill.energy > Maximum:
                Maximum = bill.energy
            if bill.energy < Minimum:
                Minimum = bill.energy
            if bill.energy == 0:
               BreakIndicator = True
               break
            Percentage.append(float(Change) / bill.energy)
        if BreakIndicator:
            continue
        TotalChangePercentage = 0
        #Temp.append(str(Percentage))
        for i in Percentage:
            TotalChangePercentage += i
        AverageChange = (TotalChangePercentage / len(Percentage)) * 100
        MeterPercentList.append([member.mbrsep, member.name, Maximum / 100.0, Minimum / 100.0, AverageChange])
    MeterPercentList.sort(key = lambda i: i[4])
    for i in MeterPercentList:
        Temp.append(i[0].ljust(20) + i[1].ljust(30) + str(round(i[2], 2)).ljust(20) + str(round(i[3], 2)).ljust(20) + str(round(i[4], 2)) + '%')

    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericMessage=''
    
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))

@transaction.commit_on_success
def PopulateOptimumObjectReference(request):
    import django.db
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    Temp=[]
    MetersNotInCIS=[]
    MetersMultipleEntriesInCIS=[]
    MetersInvalidAMRid=[]
    MetersUpdatedInOptimum=[]
    MetersCreatedInOptimum=[]

    Meters=list(AMR.Serialnumber.objects.all().order_by('serialnumber'))
    for meter in Meters:
        UpdateIndicator=False
        NewIndicator=False
        CISMeter=CIS.Meterdetl.objects.filter(meter=str(meter.serialnumber).rjust(12, '0'))
        if(not CISMeter):
            MetersNotInCIS.append(meter)
            continue
        elif(len(CISMeter) > 1):
            MetersMultipleEntriesInCIS.append(meter)
            continue
        else:
            CISMeter=CISMeter[0]
        try:
            AMROptimumObjectReference=AMR.OptimumObjectReference.objects.filter(serialnumber=CISMeter.amrid[2:].strip())
        except:
            MetersInvalidAMRid.append(meter)
            continue
        if(AMROptimumObjectReference):
            AMROptimumObjectReference=AMROptimumObjectReference[0]
            if not AMROptimumObjectReference.meternumber ==  CISMeter.meter.lstrip('0'):
#                raise Exception(str("An Error Occurred:  AMROptimumObjectReference.meternumber ==  CISMeter.meter.lstrip('0'):  " + str(AMROptimumObjectReference.meternumber) + '        ' + str(CISMeter.meter.lstrip('0')))
                raise Exception('Error')
            if not str(AMROptimumObjectReference.serialnumber) == CISMeter.amrid[2:].strip():
                AMROptimumObjectReference.serialnumber = int(CISMeter.amrid[2:].strip())
                raise Exception('Here')
                UpdateIndicator=True
            if(not AMROptimumObjectReference.utilacctid == 1):
                AMROptimumObjectReference.utilacctid=1
                UpdateIndicator=True
            if(not AMROptimumObjectReference.meter_objectid == CISMeter.amrid[2:].strip()):
                AMROptimumObjectReference.meter_objectid = CISMeter.amrid[2:].strip()
                UpdateIndicator=True
            if(not AMROptimumObjectReference.servicelocation_objectid == CISMeter.location.lstrip('0')):
                AMROptimumObjectReference.servicelocation_objectid = CISMeter.location.lstrip('0')
                UpdateIndicator=True
            if(not AMROptimumObjectReference.customer_objectid == CISMeter.mbrsep.lstrip('0')[:-3]):
                AMROptimumObjectReference.customer_objectid = CISMeter.mbrsep.lstrip('0')[:-3]
                UpdateIndicator=True
        else:
            #otherwise, create a new OptimumObjectReference
            AMROptimumObjectReference=AMR.OptimumObjectReference()
            AMROptimumObjectReference.meternumber=CISMeter.amrid[2:].strip()
            AMROptimumObjectReference.serialnumber=int(CISMeter.amrid[2:].strip())
            AMROptimumObjectReference.utilacctid=int(1)
            AMROptimumObjectReference.meter_objectid=CISMeter.amrid[2:].strip()
            AMROptimumObjectReference.servicelocation_objectid =CISMeter.location.lstrip('0')
            AMROptimumObjectReference.customer_objectid = CISMeter.mbrsep.lstrip('0')[:-3]
            UpdateIndicator=True
            NewIndicator=True
        if(UpdateIndicator):
            AMROptimumObjectReference.updated_at = datetime.datetime.now().date()
            AMROptimumObjectReference.save()
            try:
                django.db.connections['amr'].commit()
            except:
                pass
            MetersUpdatedInOptimum.append(AMROptimumObjectReference)
        if(NewIndicator):
            MetersUpdatedInOptimum.pop()
            MetersCreatedInOptimum.append(AMROptimumObjectReference)
    spacer = '\n\n'
    Temp.append(spacer + 'No Meter Found In CIS For:')
    for meter in MetersNotInCIS:
        Temp.append(str(meter.serialnumber))
    Temp.append(spacer + 'Multiple Meters Found In CIS For:')
    for meter in MetersMultipleEntriesInCIS:
        Temp.append(str(meter.serialnumber))
    Temp.append(spacer + 'CIS Meter Found With Invalid AMRid:')
    for meter in MetersInvalidAMRid:
        Temp.append(str(meter.serialnumber))
    Temp.append(spacer + 'Meters Added to Optimum Object Reference Table:')
    for meter in MetersUpdatedInOptimum:
        Temp.append(str(meter.serialnumber))
    Temp.append(spacer + 'Meters Created in Optimum Object Reference Table:')
    for meter in MetersCreatedInOptimum:
        Temp.append(str(meter.serialnumber))

    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericReportTitle='AMR - Populate Optimum Object Reference From CIS'
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))


def GetDateTimeFromCISDate(CISDate):
    import datetime
    Decade=CISDate[0:2]
    if int(Decade) <= 50:
        CISDate='20' + CISDate
    else:
        CISDate='19' + CISDate
    ReturnDate=datetime.datetime.strptime(CISDate, '%Y%m%d')
    return ReturnDate

@transaction.commit_on_success
def CISGISCountyMismatch(request):
    import django.db
    GenericReportTitle='COOPIP CIS Report - County Mismatch'
    GenericMessage='You do not have permission to use this application.'
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    Temp=[]
    MapNumberKeyedMeterDict={}
    Meters=list(GIS.Coopgisassembly.objects.filter(compatibleunit__meterindicator=True))
    for meter in Meters:
        MeterLongMapNumber=COOPIP_Misc.GetLongMapNumber(meter)
        CISFormatMapNumber=MeterLongMapNumber.rjust(16,'0')
        MapNumberKeyedMeterDict[CISFormatMapNumber]=meter        
    LocationDetails=list(CIS.Locationdetl.objects.all())
    LocationDetailDict={}
    for i in LocationDetails:
        if not i.location in LocationDetailDict:
            LocationDetailDict[i.location]=i
            continue
        if i.imaxsoft13_path_01 > LocationDetailDict[i.location].imaxsoft13_path_01:
            LocationDetailDict[i.location]=i
    Locations=list(CIS.Locinfodetl.objects.all())  
    CountyCodeDict={'Calhoun':'01', 'Kershaw':'02', 'Lexington':'03', 'Orangeburg':'04', 'Richland':'05', 'Sumter':'06'}
    Counties=GIS.Coopgiscounty.objects.filter(name__in=CountyCodeDict) 
    MismatchedCounties=[]
    LocationsMissingFromGIS=[]
    LocationsWithoutDetails=[]
    for i in Locations:
        if not i.location in MapNumberKeyedMeterDict:
            LocationsMissingFromGIS.append(i)
            continue
        if not i.location in LocationDetailDict:
            LocationsWithoutDetails.append(i)
            continue
        Details=LocationDetailDict[i.location]
        if not Details.discdate == '000000':
            continue
        Meter=MapNumberKeyedMeterDict[i.location]
        MeterPoint=Meter.station.the_geom.transform(4326, clone=True)
        for county in Counties:
            if MeterPoint.within(county.the_geom):
                MeterCountyCode=CountyCodeDict[county.name]
                break
        if not MeterCountyCode == i.countycode:
            MismatchedCounties.append((county.name, i))
    Temp.append(str(len(MismatchedCounties))  + ' Meters have differing county codes')
    #Temp.append(str(len(LocationsMissingFromGIS)) + ' Meters in CIS not found in GIS')
    #Temp.append(str(len(LocationsWithoutDetails)) + ' Internal SEDC errors?')
    Temp.append('\n\n')
    Temp.append('Location #'.ljust(20) + 'Membersep #'.ljust(15) + 'Rate'.ljust(10) + 'Class'.ljust(10) + 'CIS County Code'.ljust(20)+ 'GIS Code')
    MismatchedCounties.sort()
    for datatuple in MismatchedCounties:
        GISCountyName=datatuple[0]
        i=datatuple[1]
        Detail=LocationDetailDict[i.location]
        Temp.append(i.location.ljust(20) + Detail.mbrsep.ljust(15) + i.rate.ljust(10) + i.class_field.ljust(10) + i.countycode.ljust(20) + GISCountyName)
    
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))




def CISReportBarcode(request):
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/intranetmainpage.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    
    
    Result = []
    form=COOPIP_Form.BuildDynamicForm(request, 'CISReportBarcode')
    COOPIP_FormCISReport.ProcessCISReportBarcodeForm(form, Username)
    form=COOPIP_FormCISReport.PopulateCISReportBarcodeForm(form)
   
    WOMonthFileName = djangoproject.settings.CISReportBarcodePDFLocation + Username + '_WOMonth.pdf'
    if(os.path.exists(WOMonthFileName)):
        CreateDateTime=str(time.ctime(os.path.getmtime(WOMonthFileName)))
        WOMonthFileLink='<a href="http://' + djangoproject.settings.COOPIPServerIPAddress + WOMonthFileName.replace('/var/www', '') + '"> ' + CreateDateTime + '</a>'
    else:
        WOMonthFileLink=''

    WOFileName = djangoproject.settings.CISReportBarcodePDFLocation + Username + '_WO.pdf'
    if(os.path.exists(WOFileName)):
        CreateDateTime=str(time.ctime(os.path.getmtime(WOFileName)))
        WOFileLink='<a href="http://' + djangoproject.settings.COOPIPServerIPAddress + WOFileName.replace('/var/www', '') + '"> ' + CreateDateTime + '</a>'
    else:
        WOFileLink=''

    APFileName = djangoproject.settings.CISReportBarcodePDFLocation + Username + '_AP.pdf'
    if(os.path.exists(APFileName)):
        CreateDateTime=str(time.ctime(os.path.getmtime(APFileName)))
        APFileLink='<a href="http://' + djangoproject.settings.COOPIPServerIPAddress + APFileName.replace('/var/www', '') + '"> ' + CreateDateTime + '</a>'
    else:
        APFileLink=''



    t = loader.get_template('/opt/coopip/djangoproject/templates/cisreportbarcode.html')
    c = Context({'form': form, 'WOMonthFileLink': WOMonthFileLink, 'WOFileLink': WOFileLink, 'APFileLink': APFileLink })
    return HttpResponse(t.render(c))


@transaction.commit_on_success
def CISReportOutdoorLightingConversion(request):
    import django.db
    GenericReportTitle='COOPIP CIS Report OutdoorLightingConversion: Detail by Date'
    GenericMessage='You do not have permission to this application.'
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    Temp=[]  
    Today=datetime.datetime.now()
    StartDate = datetime.datetime(year=Today.year, month=Today.month, day=Today.day)
    EndDate = StartDate + datetime.timedelta(days=1)
    DaysBack = 0
    while(DaysBack < 31):
        CurrentStartDate = StartDate - datetime.timedelta(days=DaysBack)
        CurrentEndDate = EndDate - datetime.timedelta(days=DaysBack)
        Rows = OutdoorLightingConversionHistoricalReport.objects.filter(DateTime__gte=CurrentStartDate).filter(DateTime__lt=CurrentEndDate).order_by('YardLightIndicator', 'YardLightRate', 'YardLightType')
        CurrentString = str(CurrentStartDate.month) + '/' + str(CurrentStartDate.day) + '/' + str(CurrentStartDate.year)
        CurrentRows = []
        YardLightQuantity = 0
        OutdoorLightQuantity = 0
        OutdoorLightBlankRowIndicator=True
        for row in Rows.exclude(GeneralStatisticIndicator=True):
            if(row.YardLightIndicator):
                CurrentRows.append('YardLight'.ljust(20) + str(row.YardLightRate).ljust(20) + str(row.YardLightType).ljust(20) + str(row.Quantity).ljust(20))
                YardLightQuantity = YardLightQuantity + row.Quantity
            else:
                if(OutdoorLightBlankRowIndicator):
                    CurrentRows.append('')
                    OutdoorLightBlankRowIndicator=False
                CurrentRows.append('OutdoorLight'.ljust(20) + str(row.YardLightRate).ljust(20) + str(row.YardLightType).ljust(20) + str(row.Quantity).ljust(20))
                OutdoorLightQuantity = OutdoorLightQuantity + row.Quantity
        Temp.append(CurrentString.ljust(20) + str('YardLights:  ' + str(YardLightQuantity)).ljust(20) + str('OutdoorLights:  ' + str(OutdoorLightQuantity)).ljust(20))
        Temp.append('Subsystem'.ljust(20) + 'Rate'.ljust(20) + 'Type'.ljust(20) + 'Quantity'.ljust(20))
        for row in CurrentRows:
            Temp.append(row)
        Temp.append('')
        Temp.append('')
        DaysBack = DaysBack + 1
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))


@transaction.commit_on_success
def CISReportMapNumbersByLocation(request):
    import django.db
    GenericReportTitle='COOPIP CIS Report: MapNumbers by Location'
    GenericMessage='You do not have permission to this application.'
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    Temp=[]  
    CISLocation = list(CIS.Locinfodetl.objects.all())
    station #call gis
    i.cycle
    i.dist
    board #unsure
    mapnumber#gis
    i.substation
    county#gis
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))

@transaction.commit_on_success
def OldMapNumbersForGeneration(request):
    """Report used by Engineering for calculating old mapnumbers."""
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    GenericReportTitle = 'CIS Old Map Number Detail'
    GenericMessage = ''
    #Perform Data Gathering Tasks
    Temp = []
    Locations = list(CIS.Locinfodetl.objects.all())
    LocationList = []
    for i in Locations:
        LocationList.append((i.cycle, i.distcode, i.location, i))
    LocationList.sort()
    Temp.append('Cycle'.rjust(10) + 'District'.rjust(10) + 'Location'.rjust(20) + 'OldMapNumber'.rjust(15) + 'Station'.rjust(15) +
                'Board District'.rjust(15) + 'Substation'.rjust(15) + 'CIS County Code'.rjust(20) + 'GIS County Name'.rjust(20))
    BoardDistricts = {}
    for i in GIS.Coopgisboarddistrict.objects.all():
        BoardDistricts[i.cisidentifier]=i.name
    Counties = {}
    for i in GIS.Coopgiscounty.objects.all():
        Counties[i.cisidentifier]=i.name
    Assemblies = {}
    for meterlocation, identifier, oldmapnumber in GIS.Coopgisassembly.objects.values_list('meterlocationnumberfromcis', 'station__id', 'oldmapnumber'):
        Assemblies[meterlocation]=(str(identifier), oldmapnumber)
    for sublist in LocationList:
        i = sublist[3]
        Cycle = sublist[0]
        District = sublist[1]
        Location = sublist[2]
        try:
            Station, OldMapNumber = Assemblies[Location]
        except KeyError:
            Station = ''
            OldMapNumber= ''
        try:
            BoardDistrict = BoardDistricts[District]
        except KeyError:
            BoardDistrict = ''
        Substation = str(i.substation)
        County = str(i.countycode)
        try:
            GISCounty = Counties[County]
        except KeyError:
            GISCounty = ''
        Temp.append(Cycle.rjust(10) + District.rjust(10) + Location.rjust(20) + OldMapNumber.rjust(15) + Station.rjust(15) + BoardDistrict.rjust(15) + Substation.rjust(15) + County.rjust(20) + GISCounty.rjust(20))
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle, 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))





@transaction.commit_on_success
def CISCapitalCreditsWithDeadPatron(request):
    import django.db
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Temp=[]
    YearsDesired=['83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','00','01','02','03','04','05','06','07','08','09','10','11']
    Capcrdmstrs = list(CIS.Capcrdmstr.objects.filter(deadpatron='01'))
    Temp.append('Mbrno'.ljust(12) + 'trancode'.ljust(10) + 'year'.ljust(5) + 'patronage'.ljust(10) + 'amt1'.ljust(10) + 'amt2'.ljust(10) + 'amt3'.ljust(10) + 'amt4'.ljust(10) + 'amt5'.ljust(10) + 
                'amt6'.ljust(10) + 'gains'.ljust(10))
    for master in Capcrdmstrs:
        Capcrddetls = list(CIS.Capcrddetl.objects.filter(mbrno=master.mbrno))
        for i in Capcrddetls:
            if(i.year_ims in YearsDesired and (i.amt1 or i.amt2 or i.amt3 or i.amt4 or i.amt5 or i.amt6 or i.gains)):
                Temp.append(str(i.mbrno).ljust(12) + str(i.trancode).ljust(10) + str(i.year_ims).ljust(5) + str(i.patronage).ljust(10) + str(i.amt1).ljust(10) + str(i.amt2).ljust(10) + str(i.amt3).ljust(10) +
                            str(i.amt4).ljust(10) + str(i.amt5).ljust(10) + str(i.amt6).ljust(10) + str(i.gains).ljust(10))
    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericReportTitle='CIS CapitalCreditsWithDeadPatron'
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))




@transaction.commit_on_success
def CISReportAdjustmentDetails(request):
    import django.db
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Temp=[]
    Temp.append('Mbrno'.ljust(15) + 'Mbrsep'.ljust(15) + 'Mchdte'.ljust(15) + 'Billmoyr'.ljust(15) + 
                    'dist'.ljust(15) + 'transnbr'.ljust(15) + 'adjdate'.ljust(15) + 'batch'.ljust(15) +
                    'rate'.ljust(15) + 'class_field'.ljust(15) + 'cycle'.ljust(15) + 'taxdistcode'.ljust(15) + 
                    'taxcode'.ljust(15) + 'meterread'.ljust(15) + 'kwh'.ljust(15) + 'slkwh'.ljust(15) +
                    'sltype'.ljust(15) + 'energy'.ljust(15) + 'fuel'.ljust(15) + 'slchg'.ljust(15) +
                    'miscchgcode'.ljust(15) + 'miscchg'.ljust(15) + 'penalty'.ljust(15) + 'weather'.ljust(15) + 
                    'tax'.ljust(15) + 'localtax'.ljust(15) + 'otherchgcode'.ljust(15) + 'otherchg'.ljust(15) + 
                    'acct'.ljust(15) + 'racct'.ljust(15) + 'buditemid'.ljust(15) + 'voucher'.ljust(15) + 
                    'conttype'.ljust(15) + 'contnbr'.ljust(15) + 'patronage'.ljust(15) + 'xferacct'.ljust(15) + 
                    'amount'.ljust(15) + 'statecode'.ljust(15) + 'franchisetax'.ljust(15) + 'comments'.ljust(35) +
                    'countycode'.ljust(15) + 'citycode'.ljust(15) + 'substation'.ljust(15) + 'ylrate'.ljust(15) + 
                    'equipment'.ljust(15) + 'grosstax'.ljust(15) + 'othertax'.ljust(15) + 'roundup'.ljust(15) +
                    'otheramt1'.ljust(15) + 'otheramt2'.ljust(15) + 'otheramt3'.ljust(15) + 'adjmoyr'.ljust(15) + 
                    'demandusage'.ljust(15) + 'billeddemand'.ljust(15) + 'measureddmd'.ljust(15) + 'generation'.ljust(15) + 
                    'transmission'.ljust(15) + 'distribution'.ljust(15) + 'environmental'.ljust(15) + 'franchisefee'.ljust(15) + 
                    'todgeneration_001'.ljust(20) + 'todgeneration_002'.ljust(20) + 'todtransmision_001'.ljust(20) + 'todtransmision_002'.ljust(20) +
                    'toddistributon_001'.ljust(20) + 'toddistributon_002'.ljust(20) + 'todenvirnmentl_001'.ljust(20) + 'todenvirnmentl_002'.ljust(20) +
                    'todfranchisefe_001'.ljust(20) + 'todfranchisefe_002'.ljust(20) + 'systemtime'.ljust(40) + 'sourcecode'.ljust(25) + 
                    'sourceseq'.ljust(15) + 'ldevnbr'.ljust(15) + 'postcode'.ljust(15) + 'investment'.ljust(15) + 'equiprentalchg'.ljust(15) + 
                    'rental'.ljust(15) + 'leadkvaramt'.ljust(15) + 'lagkvaramt'.ljust(15) + 'cpdemand'.ljust(15) + 'cpdemandamt'.ljust(15) + 
                    'rebateamt'.ljust(15) + 'rebateusage'.ljust(15) + 'pca'.ljust(15) + 'usageamt'.ljust(15) + 'dmdamt'.ljust(15) + 
                    'kva'.ljust(15) + 'kvar'.ljust(15) + 'lokvar'.ljust(15) + 'imaxsoft13_path_01'.ljust(23) + 'imaxsoft13_path_02'.ljust(23) + 
                    'imaxsoft13_path_03'.ljust(23) + 'imaxsoft13_path_04'.ljust(23) + 'imaxsoft13_path_05'.ljust(23) + 'imaxsoft13_path_06'.ljust(23) +
                    'imaxsoft13_seq_no'.ljust(23) + 'COOPIPCreateDateTime'.ljust(40) + 'Multiple Adjustments'.ljust(30) + 
                    'MatchesInCIS'.ljust(15) + 'CIS Match Time'.ljust(15) + 'CIS Match History')
                
    Adjustments=COOPIPAdjdetl.objects.all().order_by('billmoyr', 'mbrno')
    for i in Adjustments:
        Temp.append(str(i.mbrno).ljust(15) + str(i.mbrsep).ljust(15) + str(i.mchdte).ljust(15) + str(i.billmoyr).ljust(15) +
                    str(i.dist).ljust(15) + str(i.transnbr).ljust(15) + str(i.adjdate).ljust(15) + str(i.batch).ljust(15) +
                    str(i.rate).ljust(15) + str(i.class_field).ljust(15) + str(i.cycle).ljust(15) + str(i.taxdistcode).ljust(15) + 
                    str(i.taxcode).ljust(15) + str(i.meterread).ljust(15) + str(i.kwh).ljust(15) + str(i.slkwh).ljust(15) +
                    str(i.sltype).ljust(15) + str(i.energy).ljust(15) + str(i.fuel).ljust(15) + str(i.slchg).ljust(15) +
                    str(i.miscchgcode).ljust(15) + str(i.miscchg).ljust(15) + str(i.penalty).ljust(15) + str(i.weather).ljust(15) + 
                    str(i.tax).ljust(15) + str(i.localtax).ljust(15) + str(i.otherchgcode).ljust(15) + str(i.otherchg).ljust(15) + 
                    str(i.acct).ljust(15) + str(i.racct).ljust(15) + str(i.buditemid).ljust(15) + str(i.voucher).ljust(15) + 
                    str(i.conttype).ljust(15) + str(i.contnbr).ljust(15) + str(i.patronage).ljust(15) + str(i.xferacct).ljust(15) + 
                    str(i.amount).ljust(15) + str(i.statecode).ljust(15) + str(i.franchisetax).ljust(15) + str(i.comments).ljust(35) +
                    str(i.countycode).ljust(15) + str(i.citycode).ljust(15) + str(i.substation).ljust(15) + str(i.ylrate).ljust(15) + 
                    str(i.equipment).ljust(15) + str(i.grosstax).ljust(15) + str(i.othertax).ljust(15) + str(i.roundup).ljust(15) +
                    str(i.otheramt1).ljust(15) + str(i.otheramt2).ljust(15) + str(i.otheramt3).ljust(15) + str(i.adjmoyr).ljust(15) + 
                    str(i.demandusage).ljust(15) + str(i.billeddemand).ljust(15) + str(i.measureddmd).ljust(15) + str(i.generation).ljust(15) + 
                    str(i.transmission).ljust(15) + str(i.distribution).ljust(15) + str(i.environmental).ljust(15) + str(i.franchisefee).ljust(15) + 
                    str(i.todgeneration_001).ljust(20) + str(i.todgeneration_002).ljust(20) + str(i.todtransmision_001).ljust(20) + str(i.todtransmision_002).ljust(20) +
                    str(i.toddistributon_001).ljust(20) + str(i.toddistributon_002).ljust(20) + str(i.todenvirnmentl_001).ljust(20) + str(i.todenvirnmentl_002).ljust(20) +
                    str(i.todfranchisefe_001).ljust(20) + str(i.todfranchisefe_002).ljust(20) + str(i.systemtime).ljust(40) + str(i.sourcecode).ljust(25) + 
                    str(i.sourceseq).ljust(15) + str(i.ldevnbr).ljust(15) + str(i.postcode).ljust(15) + str(i.investment).ljust(15) + str(i.equiprentalchg).ljust(15) + 
                    str(i.rental).ljust(15) + str(i.leadkvaramt).ljust(15) + str(i.lagkvaramt).ljust(15) + str(i.cpdemand).ljust(15) + str(i.cpdemandamt).ljust(15) + 
                    str(i.rebateamt).ljust(15) + str(i.rebateusage).ljust(15) + str(i.pca).ljust(15) + str(i.usageamt).ljust(15) + str(i.dmdamt).ljust(15) + 
                    str(i.kva).ljust(15) + str(i.kvar).ljust(15) + str(i.lokvar).ljust(15) + str(i.imaxsoft13_path_01).ljust(23) + str(i.imaxsoft13_path_02).ljust(23) + 
                    str(i.imaxsoft13_path_03).ljust(23) + str(i.imaxsoft13_path_04).ljust(23) + str(i.imaxsoft13_path_05).ljust(23) + str(i.imaxsoft13_path_06).ljust(23) +
                    str(i.imaxsoft13_seq_no).ljust(23) + str(i.COOPIPCreateDateTime).ljust(40) + str(i.MultipleAdjustmentsOnSameMemberSeperatorIndicator).ljust(30) + 
                    str(i.CountOfMatchesInCIS).ljust(15) + str(i.CountOfMatchesInCISDateTime).ljust(15) + str(i.CountOfMatchesInCISHistoryText))


    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericReportTitle='CIS Report Adjustment Details [DEVELOPMENT]'
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))



@transaction.commit_on_success
def CISReportAdjustmentDetailsTest(request):
    import django.db
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(request, ['COOPIP_CISUser', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Temp=[]
    Temp.append('Mbrno'.ljust(15) + 'Mbrsep'.ljust(15) +  'Billmoyr'.ljust(15) + 
                    'dist'.ljust(15) + 'transnbr'.ljust(15) + 'adjdate'.ljust(15) + 'batch'.ljust(15) +
                    'rate'.ljust(15) + 'class_field'.ljust(15) + 'cycle'.ljust(15) + 'taxdistcode'.ljust(15) + 
                    'kwh'.ljust(15) + 'slkwh'.ljust(15) +
                    'sltype'.ljust(15) + 'energy'.ljust(15) + 'fuel'.ljust(15) + 'slchg'.ljust(15) +
                    'miscchgcode'.ljust(15) + 'miscchg'.ljust(15) + 'penalty'.ljust(15) + 
                    'tax'.ljust(15) + 'localtax'.ljust(15) + 'otherchgcode'.ljust(15) + 'otherchg'.ljust(15) + 
                    'acct'.ljust(15) + 'racct'.ljust(15) + 'buditemid'.ljust(15) + 'voucher'.ljust(15) + 
                    'contnbr'.ljust(15) + 'patronage'.ljust(15) + 'xferacct'.ljust(15) + 
                    'amount'.ljust(15) + 'statecode'.ljust(15) + 'comments'.ljust(35) +
                    'countycode'.ljust(15) + 'citycode'.ljust(15) + 'substation'.ljust(15) + 'ylrate'.ljust(15) + 
                    'othertax'.ljust(15) + 'roundup'.ljust(15) +
                    'adjmoyr'.ljust(15) + 
                    'demandusage'.ljust(15) + 'measureddmd'.ljust(15) + 'generation'.ljust(15) + 
                    'todenvirnmentl_001'.ljust(20) + 'todenvirnmentl_002'.ljust(20) +
                    'systemtime'.ljust(40) + 'sourcecode'.ljust(25) + 
                    'ldevnbr'.ljust(15) + 
                    'imaxsoft13_path_01'.ljust(23) + 'imaxsoft13_path_02'.ljust(23) + 
                    'imaxsoft13_path_03'.ljust(23) + 'imaxsoft13_path_04'.ljust(23) + 'imaxsoft13_path_05'.ljust(23) + 'imaxsoft13_path_06'.ljust(23) +
                    'imaxsoft13_seq_no'.ljust(23) + 'COOPIPCreateDateTime'.ljust(40) + 'Multiple Adjustments'.ljust(30) + 
                    'MatchesInCIS'.ljust(15) + 'CIS Match Time'.ljust(15) + 'CIS Match History')
                
    #Adjustments=COOPIPAdjdetl.objects.all().order_by('billmoyr', 'mbrno')
    #Varioud filters:
    #Adjustments=COOPIPAdjdetl.objects.all().filter(comments__icontains='DELINQUENT PENALTY').order_by('billmoyr', 'mbrno')
    #Adjustments=COOPIPAdjdetl.objects.all().exclude(comments__icontains='DELINQUENT PENALTY').filter(comments__icontains='void').order_by('billmoyr', 'mbrno')
    #Adjustments=COOPIPAdjdetl.objects.all().exclude(comments__icontains='DELINQUENT PENALTY').exclude(comments__icontains='void').filter(comments__icontains='trip').order_by('billmoyr', 'mbrno')
    #Adjustments=COOPIPAdjdetl.objects.all().exclude(comments__icontains='DELINQUENT PENALTY').exclude(comments__icontains='void').exclude(comments__icontains='trip').filter(comments__icontains='transfer').order_by('billmoyr', 'mbrno')
    #Adjustments=COOPIPAdjdetl.objects.all().exclude(comments__icontains='DELINQUENT PENALTY').exclude(comments__icontains='void').exclude(comments__icontains='trip').exclude(comments__icontains='transfer').filter(comments__icontains='ret').order_by('billmoyr', 'mbrno')
    #Adjustments=COOPIPAdjdetl.objects.all().exclude(comments__icontains='DELINQUENT PENALTY').exclude(comments__icontains='void').exclude(comments__icontains='trip').exclude(comments__icontains='transfer').exclude(comments__icontains='ret').filter(comments__icontains='charge').order_by('billmoyr', 'mbrno')
    #Adjustments=COOPIPAdjdetl.objects.all().exclude(comments__icontains='DELINQUENT PENALTY').exclude(comments__icontains='void').exclude(comments__icontains='trip').exclude(comments__icontains='transfer').exclude(comments__icontains='ret').exclude(comments__icontains='charge').filter(comments__icontains='drawer').order_by('billmoyr', 'mbrno')
    Adjustments=COOPIPAdjdetl.objects.all().exclude(comments__icontains='DELINQUENT PENALTY').exclude(comments__icontains='void').exclude(comments__icontains='trip').exclude(comments__icontains='transfer').exclude(comments__icontains='ret').exclude(comments__icontains='charge').exclude(comments__icontains='drawer').filter(comments__icontains='take').order_by('billmoyr', 'mbrno')

   
    for i in Adjustments:
        Temp.append(str(i.mbrno).ljust(15) + str(i.mbrsep).ljust(15) +  str(i.billmoyr).ljust(15) +
                    str(i.dist).ljust(15) + str(i.transnbr).ljust(15) + str(i.adjdate).ljust(15) + str(i.batch).ljust(15) +
                    str(i.rate).ljust(15) + str(i.class_field).ljust(15) + str(i.cycle).ljust(15) + str(i.taxdistcode).ljust(15) + 
                    str(i.kwh).ljust(15) + str(i.slkwh).ljust(15) +
                    str(i.sltype).ljust(15) + str(i.energy).ljust(15) + str(i.fuel).ljust(15) + str(i.slchg).ljust(15) +
                    str(i.miscchgcode).ljust(15) + str(i.miscchg).ljust(15) + str(i.penalty).ljust(15) + 
                    str(i.tax).ljust(15) + str(i.localtax).ljust(15) + str(i.otherchgcode).ljust(15) + str(i.otherchg).ljust(15) + 
                    str(i.acct).ljust(15) + str(i.racct).ljust(15) + str(i.buditemid).ljust(15) + str(i.voucher).ljust(15) + 
                    str(i.contnbr).ljust(15) + str(i.patronage).ljust(15) + str(i.xferacct).ljust(15) + 
                    str(i.amount).ljust(15) + str(i.statecode).ljust(15) + str(i.comments).ljust(35) +
                    str(i.countycode).ljust(15) + str(i.citycode).ljust(15) + str(i.substation).ljust(15) + str(i.ylrate).ljust(15) + 
                    str(i.othertax).ljust(15) + str(i.roundup).ljust(15) +
                    str(i.adjmoyr).ljust(15) + 
                    str(i.demandusage).ljust(15) + str(i.measureddmd).ljust(15) + str(i.generation).ljust(15) + 
                    str(i.todenvirnmentl_001).ljust(20) + str(i.todenvirnmentl_002).ljust(20) +
                    str(i.systemtime).ljust(40) + str(i.sourcecode).ljust(25) + 
                    str(i.ldevnbr).ljust(15) +
                    str(i.imaxsoft13_path_01).ljust(23) + str(i.imaxsoft13_path_02).ljust(23) + 
                    str(i.imaxsoft13_path_03).ljust(23) + str(i.imaxsoft13_path_04).ljust(23) + str(i.imaxsoft13_path_05).ljust(23) + str(i.imaxsoft13_path_06).ljust(23) +
                    str(i.imaxsoft13_seq_no).ljust(23) + str(i.COOPIPCreateDateTime).ljust(40) + str(i.MultipleAdjustmentsOnSameMemberSeperatorIndicator).ljust(30) + 
                    str(i.CountOfMatchesInCIS).ljust(15) + str(i.CountOfMatchesInCISDateTime).ljust(15) + str(i.CountOfMatchesInCISHistoryText))


    Preformatted='\n'.join(Temp)
    #End Data Gathering Tasks
    GenericReportTitle='CIS Report Adjustment Details [DEVELOPMENT]'
    GenericMessage=''
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 'GenericReportTitle': GenericReportTitle,
                 'GenericMessage': GenericMessage})
    return HttpResponse(t.render(c))
